import tushare as ts
import pymysql
from sqlalchemy import create_engine

import pandas as pd

def connect_db():
        return pymysql.connect(host='mysql', port=3306, user='root', password='qazasdedc123', database='stocks', charset='utf8', use_unicode=True)

#def insert_file_rec(self, file_name, file_md5):
#    con = connect_db()
#    cur = con.cursor()
#    try:
#        sqlStr = ("INSERT INTO `stocks`.`realtime_quotes` (`code`, `name`, `open`, `pre_close`, `price`, `high`, `low`, `bid`, `ask`, `volume`, `amount`, `b1_v`, `b1_p`, `b2_v`, `b2_p`, `b3_v`, `b3_p`, `b4_v`, `b4_p`, `b5_v`, `b5_p`, `a1_v`, `a1_p`, `a2_v`, `a2_p`, `a3_v`, `a3_p`, `a4_v`, `a4_p`, `a5_v`, `a5_p`, `date_time`)"
#                  + "VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'",())
#        cur.execute(sqlStr)
#        con.commit()
#    except:
#        con.rollback()
#        raise
#    finally:
#        cur.close()
#        con.close()

df = ts.get_realtime_quotes(['603301','600624','603010']) #Single stock symbol
#print type(df)

# col
#print df['name']
# row
#print df.ix[0]
#print df.T
# index
#print df


DB_USER = 'root'
DB_PASS = 'qazasdedc123'
DB_HOST = 'mysql'
DB_PORT = 3306
DATABASE = 'stocks'

connect_info = 'mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(DB_USER, DB_PASS, DB_HOST, DB_PORT, DATABASE)  #1
engine = create_engine(connect_info)

sql = "select * from realtime_quotes;";

db = connect_db()

try:
    df.to_sql(name='realtime_quotes', con=engine,if_exists='append',index=False,chunksize=1)
except:
    print "DB error\n"
    print df

#da = pd.read_sql(sql,db)
#print da
